import util.env_utils as env_utils
import util.log_utils as log_utils
import util.db_utils as db_utils


__logger = log_utils.setup_logger(env_utils.get_relative_path_and_name(__file__))

def setup_database():
    try:
        handle_t_project_info()
        handle_t_employee_info()
        handle_t_work_hour_records()
    except Exception as e:
        __logger.error(f'发生错误: {e}')
        raise e # 重新抛出异常，以便上层调用者可以捕获并处理
    finally:
        db_utils.close_conn_and_cursor()


def handle_t_project_info():
    conn = db_utils.get_conn()
    cursor = db_utils.get_cursor()
    # 检查表是否存在
    v_check_table_query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 't_project_info')"
    cursor.execute(v_check_table_query)
    v_table_exists = cursor.fetchone()[0]

    if not v_table_exists:
        # 创建表
        v_create_table_query = '''
        CREATE TABLE t_project_info (
    project_id SERIAL PRIMARY KEY,
    project_code VARCHAR(50) NOT NULL,    		-- 项目编号
    project_name TEXT NOT NULL,   				-- 项目名称
    organization TEXT,    						-- 归属公司
    department TEXT,      						-- 项目归属部门
    project_status TEXT,  						-- 项目状态
    project_manager TEXT, 						-- 项目负责人
    project_manager_code VARCHAR(50),       	-- 项目负责人编号
    project_type TEXT,    						-- 项目类型
    pre_approval_date DATE,       				-- 预立项时间
    signing_date DATE,    						-- 签约时间
    signing_status TEXT,  						-- 签约状态
    signing_date2 DATE,   						-- 签约时间2
    creation_time TIMESTAMP,      				-- 创建时间
    approval_date DATE,   						-- 立项时间
    termination_date DATE,						-- 项目终止时间
    pre_investment_limit NUMERIC(15, 2),  		-- 预投入限额
    pre_investment_total NUMERIC(15, 2),  		-- 预投入总额
    project_source TEXT,  						-- 项目来源
    parent_project_code VARCHAR(50),      		-- 关联主项目编号
    is_main_project VARCHAR(1), 		-- 是否是主项目
    expected_sales_amount NUMERIC(15, 2), 		-- 预计销售金额
    customer_name TEXT    						-- 客户名称
);

-- 单列索引
CREATE INDEX idx_t_project_project_code ON t_project_info (project_code);
CREATE INDEX idx_t_project_project_name ON t_project_info (project_name);
CREATE INDEX idx_t_project_organization ON t_project_info (organization);
CREATE INDEX idx_t_project_project_manager_code ON t_project_info (project_manager_code);
CREATE INDEX idx_t_project_project_status ON t_project_info (project_status);
CREATE INDEX idx_t_project_project_type ON t_project_info (project_type);
CREATE INDEX idx_t_project_project_source ON t_project_info (project_source);
CREATE INDEX idx_t_project_pre_approval_date ON t_project_info (pre_approval_date);
CREATE INDEX idx_t_project_approval_date ON t_project_info (approval_date);
CREATE INDEX idx_t_project_creation_time ON t_project_info (creation_time);

-- 添加字段注释
COMMENT ON COLUMN t_project_info.project_code IS '项目编号';
COMMENT ON COLUMN t_project_info.project_name IS '项目名称';
COMMENT ON COLUMN t_project_info.organization IS '归属公司';
COMMENT ON COLUMN t_project_info.project_manager IS '项目负责人';
COMMENT ON COLUMN t_project_info.project_status IS '项目状态';
COMMENT ON COLUMN t_project_info.project_type IS '项目类型';
COMMENT ON COLUMN t_project_info.project_source IS '项目来源';
COMMENT ON COLUMN t_project_info.pre_approval_date IS '预立项时间';
COMMENT ON COLUMN t_project_info.approval_date IS '立项时间';
COMMENT ON COLUMN t_project_info.creation_time IS '创建时间';
        '''
        cursor.execute(v_create_table_query)
        __logger.info('表 t_project_info 创建成功')

    if v_table_exists:
        # 清理表中全部数据
        v_delete_query = "TRUNCATE TABLE t_project_info RESTART IDENTITY"
        cursor.execute(v_delete_query)
        conn.commit()
        __logger.info('表 t_project_info 数据清理成功')


def handle_t_employee_info():
    conn = db_utils.get_conn()
    cursor = db_utils.get_cursor()
    # 检查表是否存在
    v_check_table_query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 't_employee_info')"
    cursor.execute(v_check_table_query)
    v_table_exists = cursor.fetchone()[0]

    if not v_table_exists:
        # 创建表
        v_create_table_query = '''
        CREATE TABLE t_employee_info (
    employ_id SERIAL PRIMARY KEY,
    employee_code VARCHAR(50) ,    			-- 人员编号
    employee_name TEXT NOT NULL,   					-- 人员姓名
    organization TEXT,     							-- 人员所属公司
    department TEXT,       							-- 人员所属部门
    employee_status TEXT,  							-- 人员状态
    job_position TEXT,     							-- 人员岗位
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 记录创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP   -- 记录更新时间
);

-- 索引（命名规则：idx_简化表名_字段缩写）
CREATE INDEX idx_emp_code ON t_employee_info (employee_code);       -- 人员编号
CREATE INDEX idx_emp_name ON t_employee_info (employee_name);       -- 人员姓名
CREATE INDEX idx_emp_cmp ON t_employee_info (organization); 		-- 人员所属公司
CREATE INDEX idx_emp_dept ON t_employee_info (department);  		-- 人员所属部门
CREATE INDEX idx_emp_sts ON t_employee_info (employee_status);      -- 人员状态
CREATE INDEX idx_emp_pos ON t_employee_info (job_position); 		-- 人员岗位       
        '''
        cursor.execute(v_create_table_query)
        __logger.info('表 t_employee_info 创建成功')

    if v_table_exists:
        # 清理表中全部数据
        v_delete_query = "TRUNCATE TABLE t_employee_info RESTART IDENTITY"
        cursor.execute(v_delete_query)
        conn.commit()
        __logger.info('表 t_employee_info 数据清理成功')


def handle_t_work_hour_records():
    conn = db_utils.get_conn()
    cursor = db_utils.get_cursor()
    # 检查表是否存在
    v_check_table_query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 't_work_hour_records')"
    cursor.execute(v_check_table_query)
    v_table_exists = cursor.fetchone()[0]

    if not v_table_exists:
        # 创建表
        v_create_table_query = '''
        CREATE  TABLE  t_work_hour_records (
     record_id SERIAL PRIMARY  KEY, 				-- 自增主键
     organization TEXT,    							-- 所属组织
     organization_code VARCHAR (50),				-- 所属组织编号
     employee_name TEXT,   							-- 姓名
     employee_code VARCHAR (50),      				-- 人员编号
     department TEXT,      							-- 部门
     task_name TEXT,       							-- 任务名称
     project_manager TEXT, 							-- 归属项目经理
     project_name TEXT,    							-- 归属项目
     project_code VARCHAR (50),     				-- 归属项目编号
     task_type TEXT,       							-- 任务类型
     time_report_time TIMESTAMP,   					-- 工时填报时间
     time_belong_date DATE,							-- 工时归属日期
     reported_hours NUMERIC (6,2),  				-- 对应填报工时（小时）
     approved_hours NUMERIC (6,2),  				-- 实际认定工时（小时）
     approval_time TIMESTAMP,      					-- 认定时间
     other_notes TEXT,     							-- 其他说明
     processing_notes TEXT,							-- 处理说明
     from_source TEXT,     							-- 来源
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 记录创建时间
     ex_financial_month NUMERIC(6, 0),     			-- 财务月
     ex_working_hours NUMERIC(6,2),      				-- 工时
     ex_working_days NUMERIC(6,2),       				-- 人天
     ex_employee_company TEXT, 								-- 员工所属公司
     ex_employee_department TEXT,      						-- 员工所属部门
     ex_project_company TEXT,     								-- 项目所属公司
     ex_staff_group TEXT, 										-- 人员分组
     ex_is_key_project VARCHAR(1),     						-- 关注项目
     ex_is_key_employee VARCHAR(1),    						-- 关注人员
     ex_is_special_project_type VARCHAR(1)						-- 特殊项目
 );
 -- 添加常用查询字段的索引
 CREATE  INDEX  idx_wh_employee_code ON  t_work_hour_records (employee_code);
 CREATE  INDEX  idx_wh_project_code ON  t_work_hour_records (project_code);
 create index idx_wh_time_belong_date on t_work_hour_records (time_belong_date);
 create index idx_wh_financial_month on t_work_hour_records (ex_financial_month);
        '''
        cursor.execute(v_create_table_query)
        conn.commit()
        __logger.info('表 t_work_hour_records 创建成功')

    if v_table_exists:
        # 清理表中全部数据
        v_delete_query = "TRUNCATE TABLE t_work_hour_records RESTART IDENTITY"
        cursor.execute(v_delete_query)
        conn.commit()
        __logger.info('表 t_work_hour_records 数据清理成功')




